package com.asdan.seededu.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.asdan.seededu.entity.security.School;
import com.asdan.seededu.entity.security.SubProject;
import com.asdan.seededu.entity.security.User;
import com.asdan.seededu.service.TeacherService;
import com.asdan.seededu.util.DatabaseAccess;
import com.asdan.seededu.util.MD5;
import com.asdan.seededu.util.Pager;
import com.asdan.seededu.util.Util;

public class TeacherServiceImpl extends UtilImpl  implements TeacherService {

	public Pager<User> getTeachers(String name, String schoolId,
			String cellPhone, String subProId, int pageNo) {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		List<User> teaList = new ArrayList<User>()  ;
		Pager<User> page = new Pager<User>() ;
		String hql = "" ;
		if(name !=null&&!"".equals(name)){
			hql += " AND name ='" +name +"' " ;
		}
		if(schoolId !=null&&!"".equals(schoolId)){
			hql += " AND schoolId = "+ schoolId;
		}
		
		if(cellPhone !=null&&!"".equals(cellPhone)){
			hql += " AND cellPhone ='" +cellPhone +"'" ;
			
		}
		if(subProId !=null&&!"".equals(subProId)){
			hql += "AND subProId=" + subProId ;
		}
		String sql = "select distinct  uid , name , schoolId  ,email ,cellPhone,SCHOOLID,identificationStatus,SCHOOLREGION,SCHOOLCITY,SCHOOLNAME" +
				",SCHOOLENGLISHNAME,SCHOOLADDRESS,SCHOOLURL  from v_user_school_project_subproject  where  ifdelete = 0 and studentorteacher = 1";
		try{
			String fenyesql = " LIMIT " +Util.pageCount*(pageNo-1) + ","+Util.pageCount  ;
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql + hql + fenyesql);
			System.out.println(sql + hql + fenyesql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				
				User tea= new User();
				School sch = new School() ;
				
				tea.setUid(rs.getInt("UID")) ;
				tea.setName(rs.getString("NAME")) ;
				tea.setSchoolId(rs.getInt("SCHOOLID")) ;
				tea.setCellPhone(rs.getString("CELLPHONE")) ;
				tea.setSchoolId(rs.getInt("SCHOOLID")) ;
				tea.setEmail(rs.getString("email")) ;
				tea.setIdentificationStatus(rs.getInt("identificationStatus")) ;
				
				sch.setSchoolREGION(rs.getString("SCHOOLREGION")) ;
				sch.setSchoolCITY(rs.getString("SCHOOLCITY")) ;
				sch.setSchoolNAME(rs.getString("SCHOOLNAME") ) ;
				sch.setSchoolENGLISHNAME(rs.getString("SCHOOLENGLISHNAME")) ;
				sch.setSchoolADDRESS(rs.getString("SCHOOLADDRESS")) ;
				sch.setSchoolURL(rs.getString("SCHOOLURL")) ;
				tea.setSchool(sch) ;
				teaList.add(tea) ;
				
			}
			int total = this.gettotalCount(hql +"AND ifdelete = 0" ,cn , rs , pstmt,"v_user_school_project_subproject","uid") ;
			page.setTotal(total) ;
			page.setTotalPage(total) ;
			if(teaList.size()>0){
				
				for(int i=0;i<teaList.size() ;i++){
				User tea = teaList.get(i) ;
				System.out.println(tea.getUid()) ;
				sql = "select SUBPROJECTID  , SUBPROJECTNAME from v_user_project where uid = ? ";
				pstmt = cn.prepareStatement(sql);
				pstmt.setInt(1, tea.getUid());
				rs = pstmt.executeQuery();
				List<SubProject>  subList = new ArrayList<SubProject>() ;
				while(rs.next()){
					SubProject subp = new  SubProject() ;
					subp.setSpid(rs.getInt("SUBPROJECTID")) ;
					subp.setName(rs.getString("SUBPROJECTNAME")) ;
					subList.add(subp) ;
				}
					tea.setSubProjects(subList) ;
				}
			}
			page.setDatas(teaList) ;
			System.out.println(page.getDatas().size());
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return page ;
	}

	public User getTeacherByEmailOrID(String email, int ID) {
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		User tea = null ;
		String sql = "select uid  , name , schoolId  ,email ,cellPhone ,QQ ,wchart ,weibo ," +
				" firstName,lastName,idCard,passid,passSignTime,passCloseTime" +
				",address,postCode,identificationStatus from user  where (email =? or uid = ?) AND ifdelete = 0  ";
		System.out.println(sql);
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setString(1, email);
			pstmt.setInt(2,ID ) ;
			rs = pstmt.executeQuery();
			while(rs.next()){
				tea= new User();
  				tea.setUid(rs.getInt("UID")) ;
				tea.setName(rs.getString("NAME")) ;
				tea.setSchoolId(rs.getInt("SCHOOLID")) ;
				tea.setEmail(rs.getString("EMAIL")) ;
				tea.setCellPhone(rs.getString("CELLPHONE")) ;
				tea.setQQ(rs.getString("QQ")) ;
				tea.setWchart(rs.getString("wchart")) ;
				tea.setWeibo(rs.getString("weibo")) ;
				tea.setFirstName(rs.getString("firstname")) ;
				tea.setLastName(rs.getString("lastname")) ;
				tea.setIdCard(rs.getString("idCard")) ;
				tea.setPassid(rs.getString("passid")) ;
				tea.setPassSignTime(rs.getLong("PassSignTime")) ;
				tea.setPassCloseTime(rs.getLong("passCloseTime")) ;
				tea.setAddress(rs.getString("address")) ;
				tea.setPostCode(rs.getString("postCode")) ;
				tea.setIdentificationStatus(rs.getInt("identificationStatus")) ;
			}
			if(tea!=null){
				sql = "select SPID  , SUBPROJECTNAME from v_user_project where uid = ? ";
				pstmt = cn.prepareStatement(sql);
				pstmt.setInt(1, tea.getUid());
				rs = pstmt.executeQuery();
				List<SubProject>  subList = new ArrayList<SubProject>() ;
				while(rs.next()){
					SubProject subp = new  SubProject() ;
					subp.setSpid(rs.getInt("SPID")) ;
					subp.setName(rs.getString("SUBPROJECTNAME")) ;
					subList.add(subp) ;
				}
				tea.setSubProjects(subList) ;
				
				sql = "select SCHOOLID ,SCHOOLREGION ,SCHOOLCITY ,SCHOOLNAME, SCHOOLENGLISHNAME,SCHOOLADDRESS,SCHOOLURL  from school where schoolId  = ? ";
				pstmt = cn.prepareStatement(sql);
				pstmt.setInt(1, tea.getSchoolId() );
				rs = pstmt.executeQuery();
				School sch =null ;
				while(rs.next()){
					sch = new  School() ;
					sch.setSchoolId(rs.getInt("SCHOOLID")) ;
					sch.setSchoolREGION(rs.getString("SCHOOLREGION")) ;
					sch.setSchoolCITY(rs.getString("SCHOOLCITY")) ;
					sch.setSchoolNAME(rs.getString("SCHOOLNAME") ) ;
					sch.setSchoolENGLISHNAME(rs.getString("SCHOOLENGLISHNAME")) ;
					sch.setSchoolADDRESS(rs.getString("SCHOOLADDRESS")) ;
					sch.setSchoolURL(rs.getString("SCHOOLURL")) ;
					
				}
				tea.setSchool(sch) ;
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
			}
		}
		return tea ;
	}

	

	public boolean addTeacher(User tea) {
		// TODO Auto-generated method stub
		boolean  temp = true ;
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		String pw=MD5.md5s("123456");
		tea.setPassword(pw) ;
 		String sql="insert into user(name , password , schoolId  , email , cellPhone , identificationStatus,studentorteacher) values(?,?,?,?,?,?,?) ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(sql);
			pstmt.setString(1, tea.getName());
			pstmt.setString(2, tea.getPassword()) ;
			pstmt.setInt(3, tea.getSchoolId()) ;
			pstmt.setString(4, tea.getEmail()) ;
			pstmt.setString(5, tea.getCellPhone()) ;
			pstmt.setInt(6, 0) ;
			pstmt.setInt(7, 1) ;
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			temp = false ;
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close();
				databaseAccess.close() ;
			}catch(Exception ex){
					pstmt = null ;
					cn = null ;
					temp = false ;
			}
		}
		return temp;
	}

	public boolean updateTeacher(User tea) {
		boolean temp = true ;
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		Connection cn = null;
		PreparedStatement pstmt=null;
		String sql = "update user  set  name=?  ,cellPhone=? ,QQ=?,firstName=?," +
				"lastName=?,idCard=?,passid=?,passCloseTime=?,address=?,postCode=?  where uid= ?" ;
		System.out.println(sql) ;
		try {
			cn = databaseAccess.getConn() ;
			pstmt = cn.prepareStatement(sql) ;
			pstmt.setString(1,tea.getName()) ;
			pstmt.setString(2, tea.getCellPhone()) ;
			pstmt.setString(3, tea.getQQ()) ;
			pstmt.setString(4, tea.getFirstName()) ;
			pstmt.setString(5, tea.getLastName()) ;
			pstmt.setString(6, tea.getIdCard() ) ;
			pstmt.setString(7,tea.getPassid()) ;
			pstmt.setLong(8,tea.getPassCloseTime()) ;
			pstmt.setString(9,tea.getAddress() ) ;
			pstmt.setString(10,tea.getPostCode()) ;
			pstmt.setInt(11,tea.getUid()) ;
			pstmt.executeUpdate() ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			temp = false ;
			e.printStackTrace();
		}finally{
			try {
				pstmt.close() ;
				cn.close() ;
				databaseAccess.close() ;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
		return temp;
	}

	public boolean deleteTeacher(String uid) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		boolean result = true;
		Connection cn = null;
		PreparedStatement pstmt=null;
		String hql = "UPDATE user SET ifdelete =?  WHERE UID=? ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(hql);
			pstmt.setInt(1, 1);
			pstmt.setInt(2, Integer.parseInt(uid));
			pstmt.execute();
			result = true;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			result = false ;
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close() ;
				databaseAccess.close() ;
			}catch(Exception ex){
				result = false ;
			}
		}
		return result;
	}
	
	public boolean identify(int uid, String message, int identificationStatus) {
		// TODO Auto-generated method stub
		DatabaseAccess databaseAccess = new DatabaseAccess(false);
		boolean result = true;
		Connection cn = null;
		PreparedStatement pstmt=null;
		String hql = "UPDATE user SET identification = ? ,identificationStatus = ?  WHERE UID=? ";
		try{
			cn = databaseAccess.getConn();
			pstmt = cn.prepareStatement(hql);
			pstmt.setString(1, message);
			pstmt.setInt(2, identificationStatus);
			pstmt.setInt(3, uid);
			pstmt.execute();
			result = true;
		}catch(Exception e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			result = false ;
		}finally{                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
			try{
				pstmt.close();
				cn.close() ;
				databaseAccess.close() ;
			}catch(Exception ex){
				result = false ;
			}
		}
		return result;
	}
	
	public int gettotalCount(String sql,Connection cn ,ResultSet rs ,PreparedStatement pstmt,String tablename,String tablenameid) {
		// TODO Auto-generated method stub
		return gettotalCount(sql, tablename,tablenameid, cn , rs , pstmt );
	}

	public User getTeacher(String uid) {
		// TODO Auto-generated method stub
		return null;
	}



	
}
